package com.rabbiter.si.common;

import com.rabbiter.si.entity.StudentGrade;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.util.Objects;

public class ExcelUtils {
    // 导出成绩到Excel
    public static ByteArrayInputStream exportToExcel(List<StudentGrade> grades) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 100行缓存
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = workbook.createSheet("成绩表");

            // 关键：启用跟踪所有列以便自动调整宽度
            ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();

            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"学号", "姓名", "课程ID", "课程名称", "成绩", "学期"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 填充数据
            for (int i = 0; i < grades.size(); i++) {
                Row row = sheet.createRow(i + 1);
                StudentGrade grade = grades.get(i);

                row.createCell(0).setCellValue(grade.getStudentId());
                row.createCell(1).setCellValue(grade.getStudentName());
                row.createCell(2).setCellValue(grade.getCourseId());
                row.createCell(3).setCellValue(grade.getCourseName());
                row.createCell(4).setCellValue(grade.getScore());
                row.createCell(5).setCellValue(grade.getTerm());
            }

            // 自动调整列宽（现在可以正常工作）
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
                // 解决自动调整后列宽过窄的问题
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
            }

            workbook.write(out);
            return new ByteArrayInputStream(out.toByteArray());
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    // 从Excel导入成绩（保持不变）
    public static List<StudentGrade> importFromExcel(InputStream inputStream) {
        List<StudentGrade> grades = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum();

            // 从第2行开始读取数据
            for (int i = 1; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                StudentGrade grade = new StudentGrade();
                grade.setStudentId(getStringValue(row.getCell(0)));
                grade.setStudentName(getStringValue(row.getCell(1)));
                grade.setCourseId(getStringValue(row.getCell(2)));
                grade.setCourseName(getStringValue(row.getCell(3)));
                grade.setScore(getDoubleValue(row.getCell(4)));
                grade.setTerm(getStringValue(row.getCell(5)));

                grades.add(grade);
            }
        } catch (IOException e) {
            throw new RuntimeException("导入Excel失败", e);
        }
        return grades;
    }

    private static String getStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf((long) cell.getNumericCellValue());
            default:
                return "";
        }
    }

    private static Double getDoubleValue(Cell cell) {
        if (cell == null) return 0.0;
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        try {
            return Double.parseDouble(cell.getStringCellValue());
        } catch (NumberFormatException e) {
            return 0.0;
        }
    }
}